#!/usr/bin/env python
# coding:utf-8


import sys
import logging
from config import config
from os.path import join
from copy import deepcopy
from config.config import configs
from utils.mysqlutils import MysqlUtils
from utils.commontools import convert_to_date,today_int,today_hour
from datetime import datetime, timedelta, date
from dateutil import relativedelta


reload(sys)
sys.setdefaultencoding('utf8')


class DataTableTransfer(object):
    """
    基金业务数据迁移
    """

    funds = configs['mysql_funds']  # 基金业务数据库
    data_center = configs['mysql']  # 线上中心库
    logger = None
    QUERY_SOURCE = ''  # 数据源主键全量
    QUERY_TARGET = ''  # 目标库主键全量
    TABLE = ''   # 待插入的表名
    SOURCE_ID = tuple()  # 被关联表的主键, 源表的唯一索引(考虑复合唯一索引)
    MAP_FIELDS = dict()  # 源表和目标表字段映射关系
    MAP_VALUES = dict()  # 取值的映射关系
    COLUMN_ID = ''  # 更新主键
    DEFAULT_VALUE = {}   # 默认值设置
    DATETIME = dict()  # 日期时间型字段
    IDX = ('sec_uni_code', 'source_id', 'come_source', 'org_name', 'org_uni_code')  # 查询索引字段

    @classmethod
    def work(cls):
        """
        work(入口)
        :return:
        """

        log_file = '{0}.checksum.log'.format(cls.TABLE)
        log_path = join('/', config.LOG_FILE, log_file)
        cls.logger = open(log_path, 'w')
        cls.logger.write('The data checksum is begin, table: {0} \n'.format(cls.TABLE))
        cls.check_mysql_data(cls.TABLE, cls.QUERY_SOURCE, cls.QUERY_TARGET)
        cls.logger.write('The data checksum is end, table: {0}'.format(cls.TABLE))
        cls.logger.close()


    @classmethod
    def export_mysql(cls, query, is_source=True):
        """
        导出(大智慧数据库)数据
        :param query:
        :param is_source:
        :return:
        """

        result_rows = dict()
        conn = MysqlUtils.connect(cls.funds if is_source else cls.data_center)
        cursor = MysqlUtils.common_query(conn=conn, query=query)
        if cursor and cursor.rowcount:
            rows = cursor.fetchall()
            columns = [desc[0] for desc in cursor.description]
            transformed_columns = filter(lambda column: cls.SOURCE_ID[column], cls.SOURCE_ID)
            for index, row in enumerate(rows):
                key_row = dict()
                for column_index, each_column in enumerate(columns):
                    key_row[each_column] = row[column_index]
                    if is_source and each_column in transformed_columns:
                        mapping = cls.MAP_VALUES[each_column]
                        if row[column_index] in mapping.keys():
                            key_row[each_column] = mapping[row[column_index]]
                    if each_column in cls.DATETIME  and (len(str(key_row[each_column])) != 8 or key_row[each_column] == '19000101'):
                        key_row[each_column] = None
                    # if each_column =='prize_object_code' and str(key_row[each_column]).isdigit() :
                    #     key_row[each_column]=int(key_row[each_column])
                key_arr = []
                for key_column in cls.SOURCE_ID.keys():
                    key_arr.append('' if key_row[key_column] is None else str(key_row[key_column]) )
                key = '_'.join(key_arr)
                result_rows[key] = 1
            cursor.close()
        conn.close()

        return result_rows

    @classmethod
    def check_mysql_data(cls, table, query_source, query_target):
        """
        导表
        :param table:
        :param query_source:
        :param query_target:
        :return:
        """
        rows_source = cls.export_mysql(query_source)
        print(len(rows_source))
        rows_target = cls.export_mysql(query_target, False)
        print(len(rows_target))
        exceed_data = filter(lambda row: row not in rows_source, rows_target)
        lost_data = filter(lambda row: row not in rows_target, rows_source)
        cls.logger.write('primary key is:{0} \n'.format(' '.join(cls.SOURCE_ID.keys())))
        cls.logger.write('exceed_data count is : {0} \n {1}'.format(len(exceed_data), ' '.join(map(lambda e: e + '\n', exceed_data))))
        cls.logger.write('lost_data count is: {0} \n {1}'.format(len(lost_data), ' '.join(map(lambda e: e + '\n', lost_data))))
        conn = MysqlUtils.connect(cls.data_center)
        insert_sql = ''' insert into test.etl_checksum (check_date,check_hour, check_name, lost_count,exceed_count) values (%s,%s,%s,%s,%s)'''
        args = (today_int(), today_hour(), cls.TABLE, len(lost_data), len(exceed_data),)
        MysqlUtils.common_query(conn=conn, query=insert_sql, args=args)
        conn.commit()
        conn.close()
